

*********************************************************
* Setup Code (Part B) for:
* 
* Topic: The Value of Ratings: Evidence from their Introduction in Securities Markets
* Authors: Asaf Bernstein, Carola Frydman, Eric Hilt
* 
* Code last updated: 5/23/25
* 
*************************************************************

*************************************************************
* I. Initial Code Settings/Directories
*************************************************************

clear all
* Set to folder of where do file is saved. Change if necessary.
cd "`=substr(c(current_do), 1, indexnot(c(current_do), "/", -1))'"

*************************************************************
* II. Calculate key variables, save dataset
*************************************************************	

use "BidAskSpreads.dta", clear

*Set up dates and date interactions

    sort date_bidask
    egen date_std = group(date_bidask)
    gen Post=date_std>=35
    gen weeksSinceTreat = date_std - 34
    gen post_rated = Post*rated_issue_manual
	gen trend_rated = date_std*rated_issue_manual

*Calculate bid-ask spreads

    gen spread=(Ask-Bid)*2/(Ask+Bid)
    winsor spread, generate(spr_wis) p(0.01)
    label variable spr_wis "Bid-ask spread"
    
*Use Excel-generated yields

    destring yield_bid, force replace
    generate yield_bid_rr = yield_bid if railroad==1
    generate yield_bid_nrr = yield_bid if railroad==0
    winsor yield_bid_rr, generate(ytm_bid_wis_rr) p(0.01)
    winsor yield_bid_nrr, generate(ytm_bid_wis_nrr) p(0.01)
    gen ytm_bid_wis_new=ytm_bid_wis_rr
    replace ytm_bid_wis_new = ytm_bid_wis_nrr if railroad==0
    
*Restrict to 12-week estimation window

    keep if weeksSinceTreat>=-12 & weeksSinceTreat<=12

* Pre-rating mean yield & spread

	bys id_issue_bidask: egen temp1=mean(ytm_bid_wis_new) if weeksSinceTreat<=0 & weeksSinceTreat >-12
	bys id_issue_bidask: egen PreMean_YTM_Bid =max(temp1)	
	drop temp1		
	bys id_issue_bidask: egen temp1=mean(spr_wis) if weeksSinceTreat<=0 & weeksSinceTreat >-12
	bys id_issue_bidask: egen PreMean_Spread_pct=max(temp1)	
	drop temp1	
	
* Yields of other bonds of same issuer (IV)

	*Tag the duplicates
	bys id_issue_bidask: gen n = _n 
	gen not_duplicate =  (n <= 1)
	drop n	
	bys id_issue_bidask: egen temp1=mean(ytm_bid_wis_new) if weeksSinceTreat<=0 & weeksSinceTreat >-12
	bys id_issue_bidask: egen PreMean_YTM_Bid_12 =max(temp1)	
	drop temp1		
	bys id_issuer_nl_prt: egen temp1=total(PreMean_YTM_Bid_12) if not_duplicate == 1
	bys id_issuer_nl_prt: egen issuerTotal =max(temp1)
	bys id_issuer_nl_prt: egen temp2= nvals(id_issue_bidask) if not_duplicate == 1 & PreMean_YTM_Bid_12 != .
	bys id_issuer_nl_prt: egen numIssues = max(temp2)

	*Create IV
	gen othry=(issuerTotal-cond(PreMean_YTM_Bid_12 == ., 0,PreMean_YTM_Bid_12 ))/(numIssues-cond(PreMean_YTM_Bid_12 == ., 0,1)) 
	drop issuerTotal temp1 numIssues temp2 	
	
	*Save cross-sectional variables for use in other tables	
    preserve	
	keep id_issue_bidask rated_issue_manual PreMean_YTM_Bid PreMean_Spread_pct othry
	duplicates drop
	
	save "spreads_instrument_bidask_data_cross-section.dta", replace
    restore		
		
*Filtering 

	drop if railroad == 0
	drop if n_issue_firm_pre<2
	drop if has_one_pre_post == 0
	drop railroad n_issue_firm_pre has_one_pre_post

*Create variables for regressions and save dataset for regressions
	
	gen post_othry = Post*othry
	gen trend_othry = date_std*othry	

	xtile ptiles_PreMean_Spread_pct = PreMean_Spread_pct  if spr_wis!=. & post_othry!=. , n(100)

    save "spreads_instrument_bidask_data_panel.dta", replace	
	

	

	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
